package edu.ucsd.cse135.gas.logic.reviewer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import edu.ucsd.cse135.gas.bean.reviewer.ReviewBean;
import edu.ucsd.cse135.gas.logic.Degree;
import edu.ucsd.cse135.gas.logic.applicant.Applicant;
import edu.ucsd.cse135.gas.logic.support.Database;

public class ReviewerFunctions {

	public static HashMap<String, Object> showApplication(String applicantParameter) {
		
		try {
			
			HashMap<String, Object> application = new HashMap<String, Object>();
			
			Connection conn = Database.open();
			
			String sql = "SELECT id FROM applicant WHERE mail = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, applicantParameter);
			
			ResultSet rs = preStmt.executeQuery();
			
			rs.next();
			
			Applicant applicant = new Applicant();
			
			applicant.setApplicant(rs.getInt("id"));
			
			application.put("applicant", applicant);
			
			ArrayList<Degree> degreeList = new ArrayList<Degree>();
			
			sql = "SELECT degree.month AS month, degree.year AS year, degree.gpa AS gpa, discipline.name AS discipline, " +
				  "university.name AS university, location.name AS location, title.name AS title FROM degree, location, title, " +
				  "university, discipline WHERE degree.applicant = ? AND degree.university = university.id AND " +
				  "university.location = location.id AND degree.title = title.id AND discipline.id = degree.discipline;";
			
			preStmt = conn.prepareStatement(sql);
			preStmt.setInt(1, applicant.getApplicantId());
			rs = preStmt.executeQuery();
			
			while(rs.next())
			{
				Degree degree = new Degree();
				
				degree.setMonth(rs.getInt("month"));
				degree.setYear(rs.getInt("year"));
				degree.setGpa(rs.getFloat("gpa"));
				degree.setTitle(rs.getString("title"));
				degree.setLocation(rs.getString("location"));
				degree.setUniversity(rs.getString("university"));
				degree.setDiscipline(rs.getString("discipline"));
				
				degreeList.add(degree);
			}
			
			application.put("degrees", degreeList);
			
			sql = "SELECT grade, comment, reviewer FROM reviewer_applicant WHERE applicant = ? AND graded = 1;";
			
			preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, applicantParameter);
			
			rs = preStmt.executeQuery();
			
			ArrayList<ReviewBean> reviews = new ArrayList<ReviewBean>();
			
			while(rs.next())
			{
				ReviewBean review = new ReviewBean();
				
				review.setGrade(rs.getInt("grade"));
				review.setComment(rs.getString("comment"));
				review.setReviewer(rs.getString("reviewer"));
				
				reviews.add(review);
			}
			
			application.put("reviews", reviews);
			
			return application;
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		
	}
	
	public static ArrayList<HashMap<String, String>> getApplications(String reviewer) {
		
		try {
			
			Connection conn = Database.open();
			
			String sql = "SELECT applicant.first_name, applicant.name, applicant.mail FROM " +
						 "applicant, reviewer_applicant WHERE reviewer_applicant.reviewer = ? " +
						 "AND reviewer_applicant.graded = 0 AND reviewer_applicant.applicant = applicant.mail;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, reviewer);
			
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<HashMap<String, String>> applications = new ArrayList<HashMap<String, String>>();
			
			while(rs.next())
			{
				HashMap<String, String> application = new HashMap<String, String>();
				
				application.put("firstName", rs.getString("first_name"));
				application.put("name", rs.getString("name"));
				application.put("mail", rs.getString("mail"));
				
				
				applications.add(application);
			}
			
			return applications;	
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		
	}
	
	public static ArrayList<String> chooseReviewer(String reviewer) 
	{
		try {
			
			Connection conn = Database.open();
			
			String sql = "SELECT mail FROM user_role WHERE role = 'reviewer' AND mail != ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			
			preStmt.setString(1, reviewer);
			
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<String> reviewers = new ArrayList<String>(); 
			
			while(rs.next())
			{
				reviewers.add(rs.getString("mail"));
			}
			
			return reviewers;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
	}
	
	public static int gradeApplication(int grade, String comment, String applicant)
	{
		try {
			Connection conn = Database.open();
			
			String sql = "SELECT COUNT(reviewer) AS count FROM reviewer_applicant WHERE applicant = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			
			preStmt = conn.prepareStatement(sql);
			
			preStmt.setString(1, applicant);
			
			ResultSet rs = preStmt.executeQuery();
			
			rs.next();
			
			return rs.getInt("count");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
	}
	
	public static void storeReviewer(String newReviewer, String applicant, String oldReviewer, int grade, String comment)
	{		
		try {
			
			Connection conn = Database.open();
			
			String sql = "INSERT INTO reviewer_applicant (reviewer, applicant, graded, grade, comment) VALUES (?, ?, 0, 0, 'Pending');";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, newReviewer);
			preStmt.setString(2, applicant);
			
			preStmt.executeUpdate();
		
			storeGrading(grade, comment, oldReviewer, applicant);
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
	}
	
	public static void storeCompletion(String decision, String applicant, int grade, String comment, String reviewer)
	{
	
		try
		{
			Connection conn = Database.open();
			
			String sql = "UPDATE applicant SET application_status = ? WHERE mail = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, decision);
			preStmt.setString(2, applicant);
			
			preStmt.executeUpdate();
			
			storeGrading(grade, comment, reviewer, applicant);
			
			preStmt.close();
			Database.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
	}
	
	private static void storeGrading(int grade, String comment, String reviewer, String applicant) 
	{
		try {
			Connection conn = Database.open();
			
			String sql = "UPDATE reviewer_applicant SET graded = 1, grade = ?, comment = ? WHERE reviewer = ? AND applicant = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setInt(1, grade);
			preStmt.setString(2, comment);
			preStmt.setString(3, reviewer);
			preStmt.setString(4, applicant);

			preStmt.executeUpdate();
			
			preStmt.close();
			Database.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
}
